﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DTO;

namespace DAO
{
    public class SanPhamDAO
    {
        //Nho Comment Nhung Gi Minh Lam Nhu Vay Nhe ^^
        //Lay ds sp ngoai trang chu
        public static DataTable Ds_SpIndex()
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select Top 8 * From SanPham where IsIndex = 1 ");
            dservice.Disconnect();
            return dt;
            //Doi voi store procedure
            //DataService dservice = new DataService();
            //dservice.Connect();
            //DataTable dt = dservice.ExcuteProcedureGetTable("sp_DanhSachSpMoiNhat", null, null);
            //dservice.Disconnect();
            //return dt;
        }
        //Lay ds sp moi nhat
        public static DataTable Ds_SpMoiNhat()
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select Top 15 * From SanPham Order by MaSP Desc");
            dservice.Disconnect();
            return dt;
        }
        //Lay ds tat ca cac san pham
        public static DataTable DsSanPham()
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select * From SanPham");
            dservice.Disconnect();
            return dt;
        }
        //San pham theo danh muc
        public static DataTable DsSanPhamTheoDM(int id)
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select * From SanPham where MaDM="+id);
            dservice.Disconnect();
            return dt;
        }
        //San pham theo chi tiet
        public static DataTable ChiTietSanPham(int id)
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select * From SanPham where MaSP=" + id);
            dservice.Disconnect();
            return dt;
        }
        //San pham cung loai
        public static DataTable SanPhamCungLoai(int maDM)
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select Top 5 * From SanPham where MaDM=" + maDM);
            dservice.Disconnect();
            return dt;
        }
        //Lay san pham theo thuong hieu
        public static DataTable SanPhamThuongHieu(int maTH)
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select Top 5 * From SanPham where MaTH=" + maTH);
            dservice.Disconnect();
            return dt;
        }
        //Update luot So Luot Xem San Pham
        public static int UpdateView(int maSP)
        {
            string sql = "Update SanPham Set Viewed = ((Select Viewed From SanPham Where MaSP = "+maSP+") + 1) where MaSP = "+maSP+"";
            return Dataprovider.ExcuteNonQuery(sql);
        }
        //Lay danh sach san pham xem nhieu nhat
        public static DataTable Ds_SPXemNhieu()
        {
            DataService dservice = new DataService();
            dservice.Connect();
            DataTable dt = dservice.ExcuteQueryDataTable("Select Top 5 * From SanPham Order by Viewed Desc");
            dservice.Disconnect();
            return dt;
        }

        //Lay danh sach san pham tu csdl
        public DataTable LoadDanhSachSanPham()
        {
            string sql = "select sp.MaSP,sp.TenSP,sp.MoTa,sp.LoaiDa,sp.TrongLuong,sp.GiaGoc,sp.GiaKM,sp.ChatLieu,sp.SLTon,dm.MaDM,dm.TenDM,th.MaTH,th.TenTH,sp.HinhAnh,sp.HinhAnh2,sp.HinhAnh3,sp.Viewed,sp.IsIndex from SanPham sp, DanhMuc dm, ThuongHieu th where sp.MaDM=dm.MaDM and sp.MaTH=th.MaTH";
            return Dataprovider.ExcuteQuery(sql);
        }

        //Lay thong tin san pham theo masp
        public DataTable LoadDanhSachSanPhamTheoMa(int masp)
        {
            string sql = "select sp.TenSP,sp.MoTa,sp.LoaiDa,sp.TrongLuong,sp.ChatLieu,sp.GiaGoc,sp.GiaKM,sp.SLTon,sp.MaDM,sp.MaTH,sp.HinhAnh,sp.HinhAnh2,sp.HinhAnh3,sp.Viewed,sp.IsIndex from SanPham sp where sp.MaSP=" + masp;
            return Dataprovider.ExcuteQuery(sql);
        }

        //Insert san pham vao csdl
        public int ThemSanPham(SanPhamDTO spDTO)
        {
            string sql = "Insert into SanPham(TenSP,MoTa,LoaiDa,TrongLuong,GiaGoc,GiaKM,ChatLieu,SLTon,MaDM,MaTH,HinhAnh,HinhAnh2,HinhAnh3,IsIndex) Values(N'" + spDTO.TenSP + "',N'" + spDTO.Mota + "',N'" + spDTO.Loaida + "'," + spDTO.Trongluong + "," + spDTO.Giagoc + "," + spDTO.GiaKM + ",N'" + spDTO.Chatlieu + "'," + spDTO.SlTon + "," + spDTO.MaDM + "," + spDTO.MaTH + ",'" + spDTO.HinhAnh + "','" + spDTO.HinhAnh2 + "','" + spDTO.HinhAnh3 + "'," + spDTO.IsIndex + ")";
            return Dataprovider.ExcuteNonQuery(sql);
        }

        //Load danh muc
        public DataTable LoadDM()
        {
            string sql = "select * from DanhMuc where MaDM <> 1";
            return Dataprovider.ExcuteQuery(sql);
        }

        //Load thuong hieu
        public static DataTable LoadTH()
        {
            string sql = "select * from ThuongHieu";
            return Dataprovider.ExcuteQuery(sql);
        }

        //Cap nhat thong tin san pham
        public int CapNhatSanPham(SanPhamDTO spDTO)
        {
            string sql = "Update SanPham set TenSP=N'" + spDTO.TenSP + "',GiaGoc=" + spDTO.Giagoc + ",GiaKM=" + spDTO.GiaKM + ",MoTa=N'" + spDTO.Mota + "',ChatLieu=N'" + spDTO.Chatlieu + "',TrongLuong=" + spDTO.Trongluong + ",LoaiDa=N'" + spDTO.Loaida + "',SLTon=" + spDTO.SlTon + ",MaDM=" + spDTO.MaDM + ",MaTH=" + spDTO.MaTH + ",HinhAnh='" + spDTO.HinhAnh + "',HinhAnh2='" + spDTO.HinhAnh2 + "',HinhAnh3='" + spDTO.HinhAnh3 + "',IsIndex=" + spDTO.IsIndex + " where MaSP='" + spDTO.MaSP + "'";
            return Dataprovider.ExcuteNonQuery(sql);
        }

        //tim kiem san phâm theo ten
        public static List<SanPhamDTO> TimKiemSanPham(string tensp, int madm, int math, int gia)
        {
            List<SanPhamDTO> ls = new List<SanPhamDTO>();
            if (tensp == "") return ls;

            DataTable dt = new DataTable();
            //Cau truy van sql
            string sql = @"SELECT * 
                            FROM SanPham 
                            WHERE TenSP like '%" + tensp + "%'";

            //Tim kiem theo ma danh muc
            if (madm != 0)
            {
                sql += " AND MaDM = " + madm.ToString();
            }
		public static List<SanPhamDTO> TimKiemSanPhamTheoTen(string tensp)
        {
            List<SanPhamDTO> ls = new List<SanPhamDTO>();
            if (tensp == "") return ls;

            DataTable dt = new DataTable();
            //Cau truy van sql
            string sql = @"SELECT * 
                            FROM SanPham 
                            WHERE TenSP like '%" + tensp + "%'";
            dt = Dataprovider.ExcuteQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SanPhamDTO sp = new SanPhamDTO();
                sp.MaSP = int.Parse(dt.Rows[i]["MaSP"].ToString());
                sp.TenSP = dt.Rows[i]["TenSP"].ToString();
                sp.Giagoc = int.Parse(dt.Rows[i]["GiaGoc"].ToString());
                if (dt.Rows[i]["GiaKM"].ToString().Equals(""))
                {
                    sp.GiaKM = 0;
                }
                else
                {
                    sp.GiaKM = int.Parse(dt.Rows[i]["GiaKM"].ToString());
                }
                sp.Mota = dt.Rows[i]["MoTa"].ToString();
                sp.Chatlieu = dt.Rows[i]["ChatLieu"].ToString();
                sp.Trongluong = int.Parse(dt.Rows[i]["TrongLuong"].ToString());
                sp.SlTon = int.Parse(dt.Rows[i]["SLTon"].ToString());
                sp.MaDM = int.Parse(dt.Rows[i]["MaDM"].ToString());
                sp.MaTH = int.Parse(dt.Rows[i]["MaTH"].ToString());
                sp.HinhAnh = dt.Rows[i]["HinhAnh"].ToString();
                ls.Add(sp);
            }
            return ls;
        }
            //Tim kiem theo thuong hieu
            if (math != 0)
            {
                sql += " AND MaTH = " + math.ToString();
            }

            switch (gia)
            {
                case 1:
                    sql += " AND GiaGoc < 5000000";
                    break;
                case 2:
                    sql += " AND (GiaGoc >= 5000000 AND GiaGoc < 10000000) ";
                    break;
                case 3:
                    sql += " AND (GiaGoc >= 10000000 AND GiaGoc <20000000) ";
                    break;
                case 4:
                    sql += " AND (GiaGoc >= 20000000 AND GiaGoc <50000000) ";
                    break;
                case 5:
                    sql += " AND (GiaGoc >= 50000000";
                    break;
            }

            dt = Dataprovider.ExcuteQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SanPhamDTO sp = new SanPhamDTO();
                sp.MaSP = int.Parse(dt.Rows[i]["MaSP"].ToString());
                sp.TenSP = dt.Rows[i]["TenSP"].ToString();
                sp.Giagoc = int.Parse(dt.Rows[i]["GiaGoc"].ToString());
                sp.GiaKM = int.Parse(dt.Rows[i]["GiaKM"].ToString());
                sp.Mota = dt.Rows[i]["MoTa"].ToString();
                sp.Chatlieu = dt.Rows[i]["ChatLieu"].ToString();
                sp.Trongluong = int.Parse(dt.Rows[i]["TrongLuong"].ToString());
                sp.SlTon = int.Parse(dt.Rows[i]["SLTon"].ToString());
                sp.MaDM = int.Parse(dt.Rows[i]["MaDM"].ToString());
                sp.MaTH = int.Parse(dt.Rows[i]["MaTH"].ToString());
                sp.HinhAnh = dt.Rows[i]["HinhAnh"].ToString();
                ls.Add(sp);
            }
            return ls;
        }

        public static List<SanPhamDTO> TimKiemSanPham(string tensp)
        {
            throw new NotImplementedException();
        }
    }
}
